﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;

namespace CCPSIP
{
    public class OracleKitSticker
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// 寫入禮物貼圖送禮記錄
        /// </summary>
        /// <param name="Sender">發送禮物貼圖的帳號</param>
        /// <param name="Recipient">受贈貼圖的帳號</param>
        /// <param name="StickerID">貼圖ID，目前為資料夾名稱</param>
        public static void SendStickerGift(string Sender, string Recipient, string StickerID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                DateTime reqTime = DateTime.Now;

                myTrans = connOracle.BeginTransaction();

                //【01】取消重覆的舊禮物
                //
                string updStr = "UPDATE sticker_gift SET Status='C', StatusTime=:DateTime"
                  + " WHERE Status IN ('0','2')"
                  + " AND Sender=:Sender AND Recipient=:Recipient AND StickerID=:StickerID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle, myTrans);
                updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                updCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                updCmd.Parameters.Add("Recipient", OracleType.VarChar).Value = Recipient;
                updCmd.Parameters.Add("StickerID", OracleType.VarChar).Value = StickerID;
                updCmd.ExecuteNonQuery();

                //【02】寫入送禮記錄
                //
                string insStr = "INSERT INTO sticker_gift"
                  + " (CreateTime, Status, Sender, Recipient, StickerID, StatusTime) VALUES"
                  + " (:CreateTime, :Status, :Sender, :Recipient, :StickerID, :StatusTime)";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle, myTrans);
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                insCmd.Parameters.Add("Status", OracleType.VarChar).Value = "0";
                insCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                insCmd.Parameters.Add("Recipient", OracleType.VarChar).Value = Recipient;
                insCmd.Parameters.Add("StickerID", OracleType.VarChar).Value = StickerID;
                insCmd.Parameters.Add("StatusTime", OracleType.DateTime).Value = reqTime;
                insCmd.ExecuteNonQuery();

                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得待領取的禮物貼圖GiftInfo Array
        /// </summary>
        /// <param name="Recipient">受贈貼圖的帳號</param>
        /// <returns>禮物貼圖GiftInfo Array</returns>
        public static GiftInfo[] getGiftList(string Recipient)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT CreateTime, Sender, Recipient, StickerID"
                  + " FROM sticker_gift"
                  + " WHERE Status IN ('0','2') AND Recipient=:Recipient"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Recipient", OracleType.VarChar).Value = Recipient;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<GiftInfo> resultList = new List<GiftInfo>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    resultList.Add(new GiftInfo(row));
                }
                return resultList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查是否有送禮記錄
        /// </summary>
        /// <param name="Sender">發送禮物貼圖的帳號</param>
        /// <param name="Recipient">受贈貼圖的帳號</param>
        /// <param name="StickerID">貼圖ID，目前為資料夾名稱</param>
        /// <returns>有此送禮記錄true，否則false</returns>
        public static bool hasGift(string Sender, string Recipient, string StickerID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string countStr = "SELECT COUNT(*) FROM sticker_gift"
                  + " WHERE Status IN ('0','2')"
                  + " AND Sender=:Sender AND Recipient=:Recipient AND StickerID=:StickerID";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                countCmd.Parameters.Add("Recipient", OracleType.VarChar).Value = Recipient;
                countCmd.Parameters.Add("StickerID", OracleType.VarChar).Value = StickerID;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());

                if (count > 0)
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 確認禮物貼圖(接受或拒絕)
        /// </summary>
        /// <param name="Sender">發送禮物貼圖的帳號</param>
        /// <param name="Recipient">受贈貼圖的帳號</param>
        /// <param name="StickerID">貼圖ID，目前為資料夾名稱</param>
        /// <param name="Accepted">接受true，否則false</param>
        public static void ConfirmGift(string Sender, string Recipient, string StickerID, bool Accepted)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr = "UPDATE sticker_gift SET Status=:Status, StatusTime=:DateTime"
                  + " WHERE Status IN ('0','2')"
                  + " AND Sender=:Sender AND Recipient=:Recipient AND StickerID=:StickerID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("Status", OracleType.VarChar).Value = (Accepted ? "Y" : "N");
                updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                updCmd.Parameters.Add("Recipient", OracleType.VarChar).Value = Recipient;
                updCmd.Parameters.Add("StickerID", OracleType.VarChar).Value = StickerID;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }

    }


    /// <summary>
    /// 禮物貼圖資訊
    /// </summary>
    public class GiftInfo
    {
        private DateTime _CreateTime;//贈送禮物時間
        private string _Sender;   //送禮人帳號
        private string _Recipient;//受贈貼圖的帳號
        private string _StickerID;//貼圖ID，目前為資料夾名稱

        public DateTime CreateTime { get { return this._CreateTime; } }
        public string Sender { get { return this._Sender; } }
        public string Recipient { get { return this._Recipient; } }
        public string StickerID { get { return this._StickerID; } }


        public GiftInfo(DataRow row)
        {
            _CreateTime = Convert.ToDateTime(row["CreateTime"]);
            _Sender = Convert.ToString(row["Sender"]);
            _Recipient = Convert.ToString(row["Recipient"]);
            _StickerID = Convert.ToString(row["StickerID"]);
        }
    }
}